import 'dart:convert';
import 'dart:math';

import 'package:aqua_plunge/model/Author.dart';
import 'package:aqua_plunge/model/Creation.dart';
import 'package:aqua_plunge/model/media.dart';
import 'package:flutter/cupertino.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

import '../http/app_manager.dart';
import '../model/common.dart';

class DatabaseService {
  static Database? _db;

  static final DatabaseService instance = DatabaseService._constructor();

  final String _authorTableName = 'author';

  final String _authorUserId = "userId";
  final String _authorNickName = 'nickName';
  final String _authorAvatar = "avatar";
  final String _authorGender = "gender";
  final String _authorage = 'age';
  final String _authorfollnum = 'followNum';
  final String _authorisFriend = 'isFriend';
  final String _authorabout = 'about';
  final String _authorvideoMapPaths = 'videoMapPaths';



  final String _creationTableName = "creation";

  final String _creationUserId = "userId";
  final String _creationTitle = "title";
  final String _creationTag = "tag";
  final String _creationCover = "cover";
  final String _creationContent = "content";
  final String _creationDescribe = "describe";
  final String _creationLike = 'like';
  final String _creationId = 'postId';
  final String _creationCommon = 'common';



  final String _mediaTableName = "media";

  final String _mediaUserId = "userId";
  final String _mediaUrl = "mediaUrl";
  final String _mediaTitle = "title";
  final String _mediaThumbUrl = "thumbUrl";
  final String _mediaDescribe = "describe";
  final String _mediaLike = 'like';
  final String _mediaId = "mediaId";

  DatabaseService._constructor();

  Future<Database> get database async {
    if (_db != null) return _db!;
    _db = await getDatabase();
    return _db!;
  }

  Future<Database> getDatabase() async {
    final databaseDirPath = await getDatabasesPath();
    final databasePath = join(databaseDirPath, "master_db.db");
    final database = await openDatabase(
      databasePath,
      version: 1,
      onCreate: (db, version) {
        db.execute('''
          CREATE TABLE IF NOT EXISTS $_authorTableName(
            $_authorUserId INTEGER PRIMARY KEY,
            $_authorNickName TEXT NOT NULL,
            $_authorAvatar TEXT,
            $_authorGender INTEGER,
            $_authorage INTEGER,
            $_authorfollnum INTEGER,
            $_authorisFriend INTEGER,
            $_authorabout TEXT,
            $_authorvideoMapPaths TEXT
          )
        ''');
        db.execute('''
        CREATE TABLE IF NOT EXISTS $_creationTableName(
            $_creationUserId INTEGER,
            $_creationTitle TEXT,
            $_creationContent TEXT,
            $_creationTag TEXT,
            $_creationCover TEXT,
            $_creationDescribe TEXT,
            $_creationLike INTEGER,
            $_creationId INTEGER,
            $_creationCommon TEXT
          )
        ''');
        db.execute('''
        CREATE TABLE IF NOT EXISTS $_mediaTableName(
            $_mediaUserId INTEGER,
            $_mediaId INTEGER,
            $_mediaTitle TEXT,
            $_mediaUrl TEXT,
            $_mediaThumbUrl TEXT,
            $_mediaDescribe TEXT,
            $_mediaLike INTEGER
            )
        ''');
      },
    );
    return database;
  }

  void clearAllTable() async{
    final db = await database;
    db.delete(_authorTableName);
    db.delete(_creationTableName);
    db.delete(_mediaTableName);
  }

  ///=====================

  void addAuthorList(List<Author> datalist) async {
    for (var element in datalist) {
      debugPrint('=========user:${element.userId}');
      addAuthor(element);
    }
  }

  void addAuthor(Author item) async {
    final db = await database;
    await db.insert(_authorTableName, {
      _authorUserId: item.userId,
      _authorNickName: item.nickname,
      _authorAvatar: item.avatar,
      _authorGender: item.gender,
      _authorage: item.age,
      _authorfollnum: item.followNum,
      _authorisFriend: item.isFriend == true ? 1 : 0,
      _authorabout: item.about ?? "",
      _authorvideoMapPaths: json.encode(item.videoMapPaths)
    },conflictAlgorithm: ConflictAlgorithm.ignore);
  }

  Future<List<Author>?> getAuthorList() async {
    final db = await database;
    final data = await db.query(_authorTableName);
    List<Author> list = data
        .map((item) => Author(
            userId: (item["userId"] as int).toString(),
            nickname: item["nickName"] as String,
            avatar: item["avatar"] as String,
            gender: item["gender"] as int,
            age: item["age"] as int,
            followNum: item["followNum"] as int,
            isFriend: (item["isFriend"] as int) == 1,
            about: item["about"] == null ? "" : item["about"] as String))
        .toList();
    return list;
  }

  Future<Author> getAuthorById(int userid) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db
        .query(_authorTableName, where: 'userId = ?', whereArgs: [userid]);
    var list = maps
        .map((item) => Author(
            userId: (item["userId"] as int).toString(),
            nickname: item["nickName"] as String,
            avatar: item["avatar"] as String,
            gender: item["gender"] as int,
            age: item["age"] as int,
            followNum: item["followNum"] as int,
            isFriend: (item["isFriend"] as int) == 1,
            about: item["about"] == null ? "" : item["about"] as String))
        .toList();
    return list.first;
  }

  void updateAuthorFriend(int userId, bool like) async {
    final db = await database;
    await db.update(
        _authorTableName,
        {
          _authorisFriend: like?1:0,
        },
        where: 'userId = ?',
        whereArgs: [userId]);
  }

  ///======================
  void addCreation(Creation item) async {
    final db = await database;
    await db.insert(_creationTableName, {
      _creationUserId: item.userId,
      _creationTitle: item.title,
      _creationContent: json.encode(item.content),
      _creationCover: item.cover,
      _creationDescribe: item.describe,
      _creationTag: item.tag ?? "",
      _creationLike: item.like ?? 0,
      _creationId: item.postId,
      _creationCommon: item.common ?? ""
    });
  }

  Future<List<Creation>> getCreationList() async {
    final db = await database;
    final data = await db.query(
      _creationTableName,
    );
    List<Creation> list = data
        .map((item) => Creation(
            userId: item['userId'] as int,
            title: item['title'] as String,
            tag: item['tag'] as String,
            cover: item['cover'] as String,
            content: json.decode(item['content'].toString()).cast<String>(),
            describe: item['describe'] as String,
            like: item['like'] as int,
            postId: item['postId'] as int,
            common: item['common'] as String))
        .toList();
    return list;
  }


  Future<List<Creation>> getMineCreationList(int userId) async {
    final db = await database;
    final data = await db.query(
      _creationTableName,where: 'userId = ?',whereArgs: [userId]
    );
    List<Creation> list = data
        .map((item) => Creation(
        userId: item['userId'] as int,
        title: item['title'] as String,
        tag: item['tag'] as String,
        cover: item['cover'] as String,
        content: json.decode(item['content'].toString()).cast<String>(),
        describe: item['describe'] as String,
        like: item['like'] as int,
        postId: item['postId'] as int,
        common: item['common'] as String))
        .toList();
    return list;
  }

  Future<List<Creation>> getFavoritesCreationList() async {
    final db = await database;
    final data = await db.query(
        _creationTableName,where: 'like = ?',whereArgs: [1]
    );
    List<Creation> list = data
        .map((item) => Creation(
        userId: item['userId'] as int,
        title: item['title'] as String,
        tag: item['tag'] as String,
        cover: item['cover'] as String,
        content: json.decode(item['content'].toString()).cast<String>(),
        describe: item['describe'] as String,
        like: item['like'] as int,
        postId: item['postId'] as int,
        common: item['common'] as String))
        .toList();
    return list;
  }


  Future<List<Creation>> getCreationsExcludeBlock() async {

    final db = await database;
    var blocks = await AppManager.getBlockList();
    String placeholders = List.filled(blocks.length, '?').join(', ');
    String sql = 'SELECT * FROM $_creationTableName WHERE userId NOT IN ($placeholders)';
    final data = await db.rawQuery(sql,blocks);

    List<Creation> list = data
        .map((item) => Creation(
        userId: item['userId'] as int,
        title: item['title'] as String,
        tag: item['tag'] as String,
        cover: item['cover'] as String,
        content: json.decode(item['content'].toString()).cast<String>(),
        describe: item['describe'] as String,
        like: item['like'] as int,
        postId: item['postId'] as int,
        common: item['common'] as String))
        .toList();
    return list;
  }


  Future<Creation?> getCreationByPostId(int postId) async {
    final db = await database;
    final data = await db
        .query(_creationTableName, where: 'postId = ?', whereArgs: [postId]);
    List<Creation> list = data
        .map((item) => Creation(
            userId: item['userId'] as int,
            title: item['title'] as String,
            tag: item['tag'] as String,
            cover: item['cover'] as String,
            content: json.decode(item['content'].toString()).cast<String>(),
            describe: item['describe'] as String,
            like: item['like'] as int,
            postId: item['postId'] as int,
            common: item['common'] as String))
        .toList();
    return list.first;
  }

  void updateCreationLike(int postId, int like) async {
    final db = await database;
    await db.update(
        _creationTableName,
        {
          _creationLike: like,
        },
        where: 'postId = ?',
        whereArgs: [postId]);
  }

  void updateCreationCommon(int postId, List<Common> list) async {
    final db = await database;
    await db.update(
        _creationTableName,
        {
          _creationCommon: json.encode(list),
        },
        where: 'postId = ?',
        whereArgs: [postId]);
  }

  ///===============================

  void addMedia(Media item) async {
    final db = await database;
    await db.insert(_mediaTableName, {
      _mediaUserId: item.userId,
      _mediaId: item.videoId,
      _mediaTitle: item.title,
      _mediaThumbUrl: item.thumbUrl,
      _mediaUrl: item.mediaUrl,
      _mediaDescribe: item.describe
    });
  }

  Future<List<Media>> getMediaList() async {
    final db = await database;
    final data = await db.query(
      _mediaTableName,
    );
    List<Media> list = data
        .map((item) => Media(
            userId: item['userId']==null? Random().nextInt(100000) +100000: item['userId'] as int,
            title: item['title']==null? "" : item['title'] as String,
            describe: item['describe'] == null ?"":item['describe'] as String,
            mediaUrl: item['mediaUrl'] as String,
            videoId: item['mediaId'] as int,
            like: item['like'] == null?0:item['like'] as int,
            thumbUrl: item['thumbUrl'] as String))
        .toList();
    return list;
  }

  Future<List<Media>> getMediaListExcludeBlock() async{
    final db = await database;
    var blocks = await AppManager.getBlockList();
    String placeholders = List.filled(blocks.length, '?').join(', ');
    String sql = 'SELECT * FROM $_mediaTableName WHERE userId NOT IN ($placeholders)';
    final data = await db.rawQuery(sql,blocks);

    List<Media> list = data
        .map((item) => Media(
        userId: item['userId']==null? Random().nextInt(100000) +100000: item['userId'] as int,
        title: item['title']==null? "" : item['title'] as String,
        describe: item['describe'] == null ?"":item['describe'] as String,
        mediaUrl: item['mediaUrl'] as String,
        videoId: item['mediaId'] as int,
        like: item['like'] == null?0:item['like'] as int,
        thumbUrl: item['thumbUrl'] as String))
        .toList();
    return list;

  }


  void updateMediaLike(int mediaId, int like) async {
    final db = await database;
    await db.update(
        _mediaTableName,
        {
          _mediaLike: like,
        },
        where: 'mediaId = ?',
        whereArgs: [mediaId]);
  }

  Future<Media?> getMediaByMediaId(int mediaId) async {
    final db = await database;
    final data = await db.query(
      _mediaTableName, where: 'mediaId = ?', whereArgs: [mediaId]
    );
    List<Media> list = data
        .map((item) => Media(
        userId: item['userId']==null? Random().nextInt(100000) +100000: item['userId'] as int,
        title: item['title']==null? "" : item['title'] as String,
        describe: item['describe'] == null ?"":item['describe'] as String,
        mediaUrl: item['mediaUrl'] as String,
        videoId: item['mediaId'] as int,
        like: item['like'] == null?0:item['like'] as int,
        thumbUrl: item['thumbUrl'] as String))
        .toList();
    return list.first;
  }

}
